package com.bhprod.comatule.db;

import static com.bhprod.comatule.db.DBConstants.CRITERION_TAXON_COL_TAXON_ID;
import static com.bhprod.comatule.db.DBConstants.CRITERION_TAXON_COL_TEXT;
import static com.bhprod.comatule.db.DBConstants.CRITERION_TAXON_TABLE;
import static com.bhprod.comatule.db.DBConstants.FRENCH_SPECIES_NAMES_COL_NAME_FIRST;
import static com.bhprod.comatule.db.DBConstants.FRENCH_SPECIES_NAMES_COL_NAME_TEXT;
import static com.bhprod.comatule.db.DBConstants.FRENCH_SPECIES_NAMES_COL_SPECIES_ID;
import static com.bhprod.comatule.db.DBConstants.FRENCH_SPECIES_NAMES_TABLE;
import static com.bhprod.comatule.db.DBConstants.PHOTO_COL_ID;
import static com.bhprod.comatule.db.DBConstants.PHOTO_COL_SPECIES_ID;
import static com.bhprod.comatule.db.DBConstants.PHOTO_TABLE;
import static com.bhprod.comatule.db.DBConstants.SPECIES_COL_ANECDOTE;
import static com.bhprod.comatule.db.DBConstants.SPECIES_COL_DESCRIPTION;
import static com.bhprod.comatule.db.DBConstants.SPECIES_COL_ID;
import static com.bhprod.comatule.db.DBConstants.SPECIES_COL_NAME;
import static com.bhprod.comatule.db.DBConstants.SPECIES_COL_TAXON_ID;
import static com.bhprod.comatule.db.DBConstants.SPECIES_TABLE;
import static com.bhprod.comatule.db.DBConstants.TAXON_COL_CHILDREN_TEXT;
import static com.bhprod.comatule.db.DBConstants.TAXON_COL_DESCRIPTION;
import static com.bhprod.comatule.db.DBConstants.TAXON_COL_ID;
import static com.bhprod.comatule.db.DBConstants.TAXON_COL_NAME;
import static com.bhprod.comatule.db.DBConstants.TAXON_COL_PARENT_ID;
import static com.bhprod.comatule.db.DBConstants.TAXON_TABLE;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.TreeMap;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.bhprod.comatule.model.Photo;
import com.bhprod.comatule.model.Species;
import com.bhprod.comatule.model.Taxon;

public final class DBDataSource {

	private static volatile DBDataSource INSTANCE = new DBDataSource();
	private static SQLiteDatabase DB;
	private static DBHelper DB_HELPER;
	private static int NB_PHOTOS = -1;
	private static int NB_SPECIES = -1;
	private static Random RND = new Random();

	private DBDataSource() {
	}

	public final static DBDataSource getInstance() {
		return INSTANCE;
	}

	public void open(final Context context) throws SQLException {
		if (DB_HELPER == null) {
			DB_HELPER = new DBHelper(context);
		}

		if (DB == null) {
			DB = DB_HELPER.getReadableDatabase();
		}
	}

	public void close() {
		if (DB_HELPER != null) {
			DB_HELPER.close();
		}
	}

	public SQLiteDatabase getDatabase() {
		if (DB == null) {
			throw new IllegalStateException("Database is not initialized");
		} else {
			return DB;
		}
	}

	public int countSpecies() {
		if (NB_SPECIES == -1) {
			int result = 0;

			Cursor cursor = getDatabase().query(SPECIES_TABLE, new String[] { SPECIES_COL_ID }, null, null, null, null,
					null);

			try {
				cursor.moveToFirst();
				result = cursor.getCount();
			} finally {
				cursor.close();
			}

			return result;
		} else {
			return NB_SPECIES;
		}
	}

	public int countPhotos() {
		if (NB_PHOTOS == -1) {
			int result = 0;

			Cursor cursor = getDatabase().query(PHOTO_TABLE, new String[] { PHOTO_COL_ID }, null, null, null, null,
					null);

			try {
				cursor.moveToFirst();
				result = cursor.getCount();
			} finally {
				cursor.close();
			}

			return result;
		} else {
			return NB_PHOTOS;
		}
	}

	/**
	 * Search taxons in DB by their parent taxons. If parentIds is null, returns
	 * the root taxons
	 * 
	 * @param parentIds
	 *            A list of ids of the parent taxon
	 * @return A list of taxons
	 */
	public List<Taxon> getTaxonsByParents(final List<Integer> parentIds) {
		List<Taxon> taxons = new ArrayList<Taxon>();

		String[] ids = null;

		if (parentIds != null) {
			ids = new String[parentIds.size()];
			for (int i = 0; i < parentIds.size(); i++) {
				ids[i] = Integer.toString(parentIds.get(i));
			}
		}

		Cursor cursor = getDatabase().query(
				TAXON_TABLE,
				new String[] { TAXON_COL_ID, TAXON_COL_NAME, TAXON_COL_PARENT_ID, TAXON_COL_DESCRIPTION,
						TAXON_COL_CHILDREN_TEXT },
				parentIds == null ? TAXON_COL_PARENT_ID + " IS NULL" : TAXON_COL_PARENT_ID + " IN ("
						+ makePlaceholders(ids.length) + ")", parentIds == null ? null : ids, null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				Taxon taxon = cursorToTaxon(cursor);
				taxons.add(taxon);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return taxons;
	}

	private int countSubTaxonsByParent(int id) {
		int result = 0;
		Cursor countCursor = getDatabase().rawQuery(
				"SELECT COUNT(*) FROM " + TAXON_TABLE + " WHERE " + TAXON_COL_PARENT_ID + "=" + id, null);

		try {
			countCursor.moveToFirst();
			result = countCursor.getInt(0);
		} finally {
			countCursor.close();
		}

		return result;
	}

	/**
	 * Search taxons in DB by their parent taxon.
	 * 
	 * @param parentId
	 *            Id of the parent taxon
	 * @return A list of taxons
	 */
	public List<Taxon> getTaxonsByParent(int parentId) {
		List<Taxon> taxons = new ArrayList<Taxon>();

		Cursor cursor = getDatabase().query(
				TAXON_TABLE,
				new String[] { TAXON_COL_ID, TAXON_COL_NAME, TAXON_COL_PARENT_ID, TAXON_COL_DESCRIPTION,
						TAXON_COL_CHILDREN_TEXT }, TAXON_COL_PARENT_ID + " = ?",
				new String[] { Integer.toString(parentId) }, null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				Taxon taxon = cursorToTaxon(cursor);
				taxons.add(taxon);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return taxons;
	}

	public List<Integer> getTaxonIdsByParent(int parentId) {
		List<Integer> ids = new ArrayList<Integer>();

		Cursor cursor = getDatabase().query(TAXON_TABLE, new String[] { TAXON_COL_ID }, TAXON_COL_PARENT_ID + " = ?",
				new String[] { Integer.toString(parentId) }, null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				ids.add(cursor.getInt(0));
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return ids;
	}

	public List<Taxon> getTaxonsByIds(final List<Integer> selectedIds) {
		List<Taxon> taxons = new ArrayList<Taxon>();

		List<String> ids = new ArrayList<String>(selectedIds.size());

		for (Integer i : selectedIds) {
			ids.add(i.toString());
		}

		Cursor cursor = getDatabase().query(
				TAXON_TABLE,
				new String[] { TAXON_COL_ID, TAXON_COL_NAME, TAXON_COL_PARENT_ID, TAXON_COL_DESCRIPTION,
						TAXON_COL_CHILDREN_TEXT }, TAXON_COL_ID + " IN (" + makePlaceholders(ids.size()) + ")",
				ids.toArray(new String[ids.size()]), null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				Taxon taxon = cursorToTaxon(cursor);
				taxons.add(taxon);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return taxons;
	}

	public List<Integer> getSpeciesIdsByTaxon(int taxonId) {
		List<Integer> ids = new ArrayList<Integer>();

		Cursor cursor = getDatabase().query(SPECIES_TABLE, new String[] { SPECIES_COL_ID },
				SPECIES_COL_TAXON_ID + " = ?", new String[] { Integer.toString(taxonId) }, null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				ids.add(cursor.getInt(0));
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return ids;
	}

	public List<Species> getSpeciesByIds(final ArrayList<Integer> speciesIds) {
		List<Species> speciesList = new ArrayList<Species>();

		int nbSpecies = speciesIds.size();

		String[] ids = new String[nbSpecies];
		for (int i = 0; i < nbSpecies; i++) {
			ids[i] = Integer.toString(speciesIds.get(i));
		}

		Cursor cursor = getDatabase().query(
				SPECIES_TABLE,
				new String[] { SPECIES_COL_ID, SPECIES_COL_NAME, SPECIES_COL_TAXON_ID, SPECIES_COL_DESCRIPTION,
						SPECIES_COL_ANECDOTE }, SPECIES_COL_ID + " IN (" + makePlaceholders(ids.length) + ")", ids,
				null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				Species species = cursorToSpecies(cursor);
				species.setPhotos(getPhotosBySpecies(species.getId()));
				speciesList.add(species);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return speciesList;
	}

	public Species getSpeciesById(Integer speciesId) {
		Species species = null;

		Cursor cursor = getDatabase().query(
				true,
				SPECIES_TABLE,
				new String[] { SPECIES_COL_ID, SPECIES_COL_NAME, SPECIES_COL_TAXON_ID, SPECIES_COL_DESCRIPTION,
						SPECIES_COL_ANECDOTE }, SPECIES_COL_ID + " = ?", new String[] { speciesId.toString() }, null,
				null, null, "1");
		try {
			cursor.moveToFirst();
			if (!cursor.isAfterLast()) {
				species = cursorToSpecies(cursor);
				species.setPhotos(getPhotosBySpecies(speciesId));
			}
		} finally {
			cursor.close();
		}

		return species;
	}

	public List<Photo> getPhotosBySpecies(Integer speciesId) {
		List<Photo> photos = new ArrayList<Photo>();

		Cursor cursor = getDatabase().query(PHOTO_TABLE, new String[] { PHOTO_COL_ID, PHOTO_COL_SPECIES_ID },
				PHOTO_COL_SPECIES_ID + " = ?", new String[] { speciesId.toString() }, null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				Photo photo = cursorToPhoto(cursor);
				photos.add(photo);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return photos;
	}

	public Photo getRandomPhoto() {
		Photo result = null;

		Cursor cursor = getDatabase().query(PHOTO_TABLE, new String[] { PHOTO_COL_ID, PHOTO_COL_SPECIES_ID }, null,
				null, null, null, null);

		try {
			cursor.moveToFirst();
			int val = RND.nextInt(cursor.getCount() - 1);
			for (int i = 0; i <= val; i++) {
				cursor.moveToNext();
			}
			result = cursorToPhoto(cursor);
		} finally {
			cursor.close();
		}

		return result;
	}

	public Map<String, List<String[]>> getSpeciesSortedByFrenchName() {
		Map<String, List<String[]>> result = new TreeMap<String, List<String[]>>();

		Cursor cursor = getDatabase().rawQuery(
				"SELECT DISTINCT " + FRENCH_SPECIES_NAMES_COL_NAME_TEXT + ", " + SPECIES_COL_NAME + ", "
						+ FRENCH_SPECIES_NAMES_TABLE + "." + FRENCH_SPECIES_NAMES_COL_SPECIES_ID + " FROM "
						+ FRENCH_SPECIES_NAMES_TABLE + " JOIN " + SPECIES_TABLE + " ON (" + FRENCH_SPECIES_NAMES_TABLE
						+ "." + FRENCH_SPECIES_NAMES_COL_SPECIES_ID + " = " + SPECIES_TABLE + "." + SPECIES_COL_ID
						+ ")", null);

		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String letter = cursor.getString(0).substring(0, 1);
				List<String[]> names = result.get(letter);
				if (names == null) {
					names = new ArrayList<String[]>();
				}
				names.add(new String[] { cursor.getString(0), cursor.getString(1), cursor.getString(2) });
				result.put(letter, names);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return result;
	}

	public Map<String, List<String[]>> getSpeciesSortedByLatinName() {
		Map<String, List<String[]>> result = new TreeMap<String, List<String[]>>();

		Cursor cursor = getDatabase().rawQuery(
				"SELECT DISTINCT " + SPECIES_COL_NAME + ", " + FRENCH_SPECIES_NAMES_COL_NAME_TEXT + ", "
						+ FRENCH_SPECIES_NAMES_TABLE + "." + FRENCH_SPECIES_NAMES_COL_SPECIES_ID + " FROM "
						+ FRENCH_SPECIES_NAMES_TABLE + " JOIN " + SPECIES_TABLE + " ON (" + FRENCH_SPECIES_NAMES_TABLE
						+ "." + FRENCH_SPECIES_NAMES_COL_SPECIES_ID + " = " + SPECIES_TABLE + "." + SPECIES_COL_ID
						+ ") WHERE " + FRENCH_SPECIES_NAMES_COL_NAME_FIRST + " = 'o'", null);

		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String letter = cursor.getString(0).substring(0, 1);
				List<String[]> names = result.get(letter);
				if (names == null) {
					names = new ArrayList<String[]>();
				}
				names.add(new String[] { cursor.getString(0), cursor.getString(1), cursor.getString(2) });
				result.put(letter, names);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return result;
	}

	public Map<String, List<String[]>> searchSpeciesSortedByFrenchName(final String query) {
		Map<String, List<String[]>> result = new TreeMap<String, List<String[]>>();

		Cursor cursor = getDatabase().rawQuery(
				"SELECT DISTINCT " + FRENCH_SPECIES_NAMES_COL_NAME_TEXT + ", " + SPECIES_COL_NAME + ", "
						+ FRENCH_SPECIES_NAMES_TABLE + "." + FRENCH_SPECIES_NAMES_COL_SPECIES_ID + " FROM "
						+ FRENCH_SPECIES_NAMES_TABLE + " JOIN " + SPECIES_TABLE + " ON (" + FRENCH_SPECIES_NAMES_TABLE
						+ "." + FRENCH_SPECIES_NAMES_COL_SPECIES_ID + " = " + SPECIES_TABLE + "." + SPECIES_COL_ID
						+ ") WHERE " + FRENCH_SPECIES_NAMES_COL_NAME_TEXT + " LIKE '%" + query + "%' OR "
						+ SPECIES_COL_NAME + " LIKE '%" + query + "%'", null);

		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String letter = cursor.getString(0).substring(0, 1);
				List<String[]> names = result.get(letter);
				if (names == null) {
					names = new ArrayList<String[]>();
				}
				names.add(new String[] { cursor.getString(0), cursor.getString(1), cursor.getString(2) });
				result.put(letter, names);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return result;
	}

	private int countSubSpeciesByTaxon(int id) {
		int result = 0;

		// If there is sub taxons, get their species count
		List<Integer> ids = getTaxonIdsByParent(id);
		if (ids.size() > 0) {
			for (Integer i : ids) {
				result += countSubSpeciesByTaxon(i);
			}
		} else {
			// else return taxons nb sub species
			Cursor countCursor = getDatabase().rawQuery(
					"SELECT COUNT(*) FROM " + SPECIES_TABLE + " WHERE " + SPECIES_COL_TAXON_ID + "=" + id, null);

			try {
				countCursor.moveToFirst();
				result = countCursor.getInt(0);
			} finally {
				countCursor.close();
			}
		}

		return result;
	}

	private Taxon cursorToTaxon(final Cursor cursor) {
		Taxon taxon = new Taxon();
		taxon.setId(cursor.getInt(0));
		taxon.setName(cursor.getString(1));
		taxon.setParentId(cursor.getInt(2));
		taxon.setShortDescription(cursor.getString(3));
		taxon.setChildrenText(cursor.getString(4));

		taxon.setCriteria(getCriteriaByTaxon(taxon.getId()));

		taxon.setNbSubTaxons(countSubTaxonsByParent(taxon.getId()));
		taxon.setNbSubSpecies(countSubSpeciesByTaxon(taxon.getId()));

		return taxon;
	}

	private String getCriteriaByTaxon(Integer taxonId) {
		StringBuilder result = new StringBuilder("");

		int cpt = 1;

		Cursor cursor = getDatabase().query(CRITERION_TAXON_TABLE, new String[] { CRITERION_TAXON_COL_TEXT },
				CRITERION_TAXON_COL_TAXON_ID + " = ?", new String[] { taxonId.toString() }, null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String description = cursor.getString(0);
				result.append(cpt + "/" + description);
				cursor.moveToNext();
				cpt++;
				if (!cursor.isAfterLast()) {
					result.append(" - ");
				}
			}
		} finally {
			cursor.close();
		}

		return result.toString();
	}

	private Species cursorToSpecies(final Cursor cursor) {
		Species species = new Species();
		species.setId(cursor.getInt(0));
		species.setName(cursor.getString(1));
		species.setIdParentTaxon(cursor.getInt(2));
		species.setDescription(cursor.getString(3));
		species.setAnecdote(cursor.getString(4));

		return species;
	}

	private Photo cursorToPhoto(final Cursor cursor) {
		Photo photo = new Photo();
		photo.setId(cursor.getInt(0));
		photo.setSpeciesId(cursor.getInt(1));
		return photo;
	}

	// private String unescape(String value) {
	// return value.replaceAll("\\\\n", "\\\n");
	// }

	private String makePlaceholders(int length) {
		StringBuilder sb = new StringBuilder();
		sb.append("?");
		for (int i = 1; i < length; i++) {
			sb.append(",?");
		}
		return sb.toString();
	}

}
